const { pool } = require('../config/db'); // 引入数据库连接池

class Pet {
  // 获取所有宠物信息，支持分页和过滤
  static async findAll(page = 1, limit = 10, filters = {}) {
    // 计算偏移量，用于分页
    const offset = (page - 1) * limit;
    
    // 构建基础SQL查询
    let query = `
      SELECT p.*, 
             c.name as category_name,
             CASE 
               WHEN EXISTS (SELECT 1 FROM adoption_applications aa WHERE aa.pet_id = p.id AND aa.status = 'pending') THEN 'pending'
               WHEN p.is_adopted THEN 'adopted'
               ELSE 'available'
             END as status
      FROM pets p
      LEFT JOIN pet_categories c ON p.category_id = c.id
      WHERE 1=1
    `;
    
    const queryParams = [];
    
    // 根据提供的过滤条件添加查询条件
    if (filters.category_id) {
      query += ' AND p.category_id = ?';
      queryParams.push(filters.category_id);
    }
    
    if (filters.gender) {
      query += ' AND p.gender = ?';
      queryParams.push(filters.gender);
    }
    
    if (filters.size) {
      query += ' AND p.size = ?';
      queryParams.push(filters.size);
    }
    
    if (filters.is_adopted !== undefined) {
      query += ' AND p.is_adopted = ?';
      queryParams.push(filters.is_adopted);
    }
    
    // 添加搜索功能支持
    if (filters.search) {
      query += ` AND (p.name LIKE ? OR p.breed LIKE ? OR p.description LIKE ?)`;
      const searchTerm = `%${filters.search}%`;
      queryParams.push(searchTerm, searchTerm, searchTerm);
    }
    
    // 添加排序和分页
    query += ' ORDER BY p.created_at DESC LIMIT ? OFFSET ?';
    queryParams.push(parseInt(limit), offset);
    
    try {
      // 执行查询获取宠物列表
      const [rows] = await pool.query(query, queryParams);
      
      // 构建计数查询，用于获取总记录数
      let countQuery = `
        SELECT COUNT(*) as total 
        FROM pets p
        WHERE 1=1
      `;
      const countParams = [];
      
      // 添加相同的过滤条件到计数查询
      if (filters.category_id) {
        countQuery += ' AND p.category_id = ?';
        countParams.push(filters.category_id);
      }
      
      if (filters.gender) {
        countQuery += ' AND p.gender = ?';
        countParams.push(filters.gender);
      }
      
      if (filters.size) {
        countQuery += ' AND p.size = ?';
        countParams.push(filters.size);
      }
      
      if (filters.is_adopted !== undefined) {
        countQuery += ' AND p.is_adopted = ?';
        countParams.push(filters.is_adopted);
      }
      
      // 添加搜索条件到计数查询
      if (filters.search) {
        countQuery += ` AND (p.name LIKE ? OR p.breed LIKE ? OR p.description LIKE ?)`;
        const searchTerm = `%${filters.search}%`;
        countParams.push(searchTerm, searchTerm, searchTerm);
      }
      
      // 执行计数查询
      const [countResult] = await pool.query(countQuery, countParams);
      
      // 处理图片URL
      const petsWithFullUrls = rows.map(pet => ({
        ...pet,
        image_url: pet.image_url ? pet.image_url : null
      }));
      
      // 返回宠物列表和分页信息
      return {
        pets: petsWithFullUrls,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      console.error('查询宠物列表失败:', error);
      throw new Error(error.message);
    }
  }

  // 根据ID获取宠物详情
  static async findById(id) {
    try {
      // 查询宠物信息，关联类别名称
      const [rows] = await pool.query(
        `SELECT p.*, c.name as category_name 
         FROM pets p
         JOIN pet_categories c ON p.category_id = c.id
         WHERE p.id = ?`,
        [id]
      );
      return rows[0]; // 返回第一条记录
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // 创建新宠物
  static async create(petData) {
    // 解构宠物数据
    const {
      name,
      category_id,
      breed,
      age,
      gender,
      size,
      description,
      health_status,
      behavior,
      image_url
    } = petData;

    try {
      // 插入宠物记录到数据库
      const [result] = await pool.query(
        `INSERT INTO pets (
          name, category_id, breed, age, gender, size, 
          description, health_status, behavior, image_url
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          name,
          category_id,
          breed,
          age,
          gender,
          size,
          description,
          health_status,
          behavior,
          image_url
        ]
      );

      // 返回新创建的宠物完整信息
      return this.findById(result.insertId);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // 更新宠物信息
  static async update(id, petData) {
    // 解构更新数据
    const {
      name,
      category_id,
      breed,
      age,
      gender,
      size,
      description,
      health_status,
      behavior,
      is_adopted,
      image_url
    } = petData;

    try {
      // 更新宠物记录
      await pool.query(
        `UPDATE pets SET 
          name = ?, 
          category_id = ?, 
          breed = ?, 
          age = ?, 
          gender = ?, 
          size = ?, 
          description = ?, 
          health_status = ?, 
          behavior = ?,
          is_adopted = ?,
          image_url = ?
         WHERE id = ?`,
        [
          name,
          category_id,
          breed,
          age,
          gender,
          size,
          description,
          health_status,
          behavior,
          is_adopted,
          image_url,
          id
        ]
      );

      // 返回更新后的宠物完整信息
      return this.findById(id);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // 删除宠物
  static async delete(id) {
    try {
      // 从数据库中删除宠物记录
      const [result] = await pool.query('DELETE FROM pets WHERE id = ?', [id]);
      // 返回是否成功删除（受影响的行数大于0）
      return result.affectedRows > 0;
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // 获取所有宠物类别
  static async getCategories() {
    try {
      const [rows] = await pool.query('SELECT * FROM pet_categories ORDER BY name');
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // 获取宠物总数
  static async getCount() {
    try {
      const [rows] = await pool.query('SELECT COUNT(*) as count FROM pets');
      return rows[0].count;
    } catch (error) {
      console.error('获取宠物总数失败:', error);
      throw new Error(error.message);
    }
  }

  // 根据类别获取宠物列表
  static async findByCategory(categoryId, page = 1, limit = 10) {
    // 计算偏移量，用于分页
    const offset = (page - 1) * limit;
    
    try {
      // 查询特定类别的宠物列表
      const [rows] = await pool.query(
        `SELECT p.*, c.name as category_name 
         FROM pets p
         JOIN pet_categories c ON p.category_id = c.id
         WHERE p.category_id = ?
         ORDER BY p.created_at DESC
         LIMIT ? OFFSET ?`,
        [categoryId, parseInt(limit), offset]
      );
      
      // 获取该类别下的总记录数
      const [countResult] = await pool.query(
        'SELECT COUNT(*) as total FROM pets WHERE category_id = ?',
        [categoryId]
      );
      
      // 返回宠物列表和分页信息
      return {
        pets: rows,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(error.message);
    }
  }
}

module.exports = Pet; 